package com.export.document.repository;

import com.export.document.entity.ExportRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ExportRecordRepository extends JpaRepository<ExportRecord, String> {

    /**
     * 根据数据库名称,查询表名
     *
     * @param shcemaName
     * @return
     */
    @Query(value = "select TABLE_NAME,tb.TABLE_COMMENT from information_schema.`TABLES` tb WHERE tb.TABLE_SCHEMA=?1 ", nativeQuery = true)
    List<Object[]> findTablesByShcemaName(String shcemaName);

    /**
     * 根据表名,查询表字段
     *
     * @param shcemaName
     * @param tableName
     * @return
     */
    @Query(value = "SELECT col.TABLE_SCHEMA,col.COLUMN_NAME,col.IS_NULLABLE,col.DATA_TYPE,col.CHARACTER_MAXIMUM_LENGTH,col.NUMERIC_PRECISION,col.NUMERIC_SCALE,col.COLUMN_COMMENT " +
            "from information_schema.`COLUMNS` col " +
            "WHERE col.TABLE_SCHEMA=?1 and col.TABLE_NAME=?2", nativeQuery = true)
    List<Object[]> findColumnsByTableName(String shcemaName, String tableName);

    /**
     * 查询所有数据库
     *
     * @return
     */
    @Query(value = "SELECT SCHEMA_NAME from information_schema.SCHEMATA where SCHEMA_NAME not in ('information_schema','performance_schema','sys','mysql') ", nativeQuery = true)
    List<Object[]> findDatabase();

    /**
     * 根据数据库名称+表名,查询表名
     *
     * @param shcemaName
     * @param tableName
     * @return
     */
    @Query(value = "select TABLE_NAME,tb.TABLE_COMMENT from information_schema.`TABLES` tb WHERE tb.TABLE_SCHEMA=?1 and tb.TABLE_NAME=?2 ", nativeQuery = true)
    List<Object[]> findTablesByShcemaNameAndTableName(String shcemaName, String tableName);
}
